--------------------CREATE DATABASE----------------------

CREATE DATABASE OPO
GO
USE OPO
GO
--------------------CREATE TABLE Role----------------------
	CREATE TABLE [Role](
	RoleID int identity primary key,
	RoleName nvarchar(20),
)

--------------------CREATE TABLE Account----------------------
	CREATE TABLE Account(
	AccID int identity primary key,
	Username nvarchar(50)  ,
	[Password] nvarchar(50)  ,
	RoleID int foreign key references [Role](RoleID)
	)

--------------------CREATE TABLE City----------------------
	CREATE TABLE City(
	CityID int identity primary key,
	CityName nvarchar(50)  ,
	ZipCode nvarchar(10)
	)

--------------------CREATE TABLE Branch----------------------
	CREATE TABLE Branch(
	BranchID int identity primary key,
	BranchName nvarchar(50)  ,
	BranchAddress nvarchar(1000)  ,
	BranchPhone nvarchar(20)  ,
	BranchEmail nvarchar(50)  ,
	CityID int foreign key references City(CityID)
	)
--------------------CREATE TABLE User----------------------
	CREATE TABLE [User](
	UserID int identity primary key,
	AccID int foreign key references Account(AccID),
	BranchID int foreign key references Branch(BranchID),
	[User_Name] nvarchar(100)  ,
	User_Email nvarchar(50)  ,
	User_Birthday varchar(20)   ,
	User_Address nvarchar(1000)   ,
	User_Phone nvarchar(20)  ,
	User_Gender nvarchar(20)   ,
	DateCreated varchar(20)    
	)

--------------------CREATE TABLE Customer----------------------
	CREATE TABLE Customer(
	CustID int identity primary key,
	AccID int foreign key references Account(AccID),
	Cust_Name nvarchar(100)   ,
	Cust_Email nvarchar(50)  ,
	Cust_Birthday varchar(20)  ,
	Cust_Gender nvarchar(10)   ,
	Cust_Address nvarchar(1000)  ,
	Cust_Phone nvarchar(20)   ,
	DateCreated varchar(20)  ,
	Cust_Status nvarchar(50)
	)
--------------------CREATE TABLE WeightofMail----------------------
	CREATE TABLE WeightofMail(
	WeightID int identity primary key,
	[Weight] nvarchar(50)  ,
	WeightCharge float  
	)
--------------------CREATE TABLE DistanceofMail----------------------
	CREATE TABLE DistanceofMail(
	DistanceID int identity primary key,
	Distance nvarchar(50)  ,
	DistanceCharge float  
	)
	--------------------CREATE TABLE TypeOfDeliver----------------------
	CREATE TABLE TypeofDeliver(
	DTypeID int identity primary key,
	DTypeName nvarchar(50) ,
	[Description] nvarchar(1000),
	DTypeCharge float  
	)
	--------------------CREATE TABLE TypeOfMail----------------------
	CREATE TABLE TypeofMail(
	MTypeID int identity primary key,
	MTypeName nvarchar(50)  ,
	[Description] nvarchar(1000)  
	)
	--------------------CREATE TABLE Feedback----------------------
	CREATE TABLE Feedback(
	FeedbackID int identity primary key,
	CustID int foreign key references Customer(CustID),
	DateSent varchar(20),
	Title nvarchar(100),
	Content nvarchar(1000)
	)

--------------------CREATE TABLE Mails----------------------
	CREATE TABLE Mail(
	MailID int identity(1000,1) primary key,
	BranchID int foreign key references Branch(BranchID),
	DistanceID int foreign key references DistanceofMail(DistanceID),
	MTypeID int foreign key references TypeofMail(MTypeID),
	DTypeID int foreign key references TypeofDeliver(DTypeID),
	WeightID int foreign key references WeightofMail(WeightID),
	ZipCode varchar(10),
	DateReceipt varchar(20),
	DateDelivery varchar(20),
	[Status] nvarchar(20) DEFAULT 'Pending',
	isSendEmail nvarchar(20) DEFAULT 'No',
	SenderName nvarchar(50) ,
	SenderAddress nvarchar(1000),
	SenderEmail nvarchar(50) ,
	SenderPhone varchar(20) ,
	ReceiverName nvarchar(50) ,
	ReceiverAddess nvarchar(1000) ,
	ReceiverPhone varchar(20),
	TotalFee float
	)	
	
	
--------------------INSERT DATA TABLE Role----------------------
insert into [Role] values('Admin')
insert into [Role] values('Employee')
insert into [Role] values('Customer')
--------------------INSERT DATA TABLE City----------------------
insert into City values('Ho Chi Minh city','70000')
insert into City values('Ha Noi city','10000')
insert into City values('Hai Phong city','35000')
--------------------INSERT DATA TABLE Branch----------------------
insert into Branch values('Branch A','55 HCM','0902444555','brancha@gmail.com','1')
insert into Branch values('Branch B','22 Ha Noi','0902444555','brancha@gmail.com','2')
insert into Branch values('Branch C','33 Hai Phong','0902444555','brancha@gmail.com','3')
--------------------INSERT DATA TABLE Account----------------------
insert into Account values('admin','abc123','1')
insert into Account values('employee1','abc123','2')
insert into Account values('employee2','abc123','2')
insert into Account values('employee3','abc123','2')
insert into Account values('employee4','abc123','2')
insert into Account values('guest','abc123','3')
insert into Account values('guest1','abc123','3')
insert into Account values('guest2','abc123','3')
insert into Account values('guest3','abc123','3')
--------------------CREATE TABLE Customer----------------------
insert into Customer values('6','customer','cust@yahoo.com','01/01/2011','Male','123asd','01234567','10/10/2011/','Enable')
insert into Customer values('7','customer1','cust1@yahoo.com','01/01/2011','Female','123asd','01234567','10/10/2011/','Enable')
insert into Customer values('8','customer2','cust2@yahoo.com','01/01/2011','Male','123asd','01234567','10/10/2011/','Enable')
insert into Customer values('9','customer3','cust3@yahoo.com','01/01/2011','Female','123asd','01234567','10/10/2011/','Enable')
--------------------CREATE TABLE Feedback----------------------
insert into Feedback values('1','10/10/2011','dear admin','123abc')
insert into Feedback values('2','10/10/2011','dear admin','123abc')
insert into Feedback values('3','10/10/2011','dear admin','123abc')
insert into Feedback values('4','10/10/2011','dear admin','123abc')
--------------------CREATE TABLE TypeOfDeliver----------------------
insert into TypeOfDeliver values('Normal Post','Just a normal post',2000)
insert into TypeOfDeliver values('Speed Post','Fast post',5000)
insert into TypeOfDeliver values('Free','Post Free',0)
insert into TypeOfDeliver values('Parcel Post','Parcel Post',4000)
--------------------CREATE TABLE TypeOfMail----------------------
insert into TypeOfMail values('Letter','10x12')
insert into TypeOfMail values('Postcard','12x12')
insert into TypeOfMail values('Package','23x16')
insert into TypeOfMail values('Parcel','20x20')
insert into TypeOfMail values('Aerogramme','15x15')
--------------------CREATE TABLE DistanceOfMail----------------------
insert into DistanceOfMail values('Up to 100 kms',1)
insert into DistanceOfMail values('100 to 500 kms',1.5)
insert into DistanceOfMail values('500 to 1000 kms',2)
insert into DistanceOfMail values('Above 1000 kms',2.5)
--------------------CREATE TABLE WeightOfMail----------------------
insert into WeightOfMail values('Up to 50 grams',2000)
insert into WeightOfMail values('51 to 200 grams',5000)
insert into WeightOfMail values('201 to 500 gram',8000)
insert into WeightOfMail values('Above 500 grams',10000)
--------------------CREATE TABLE Mail----------------------
insert into Mail values('1','1','1','1','1','70000','11/15/2011','11/20/2011','Received','Yes','Nguyen Truong Phat','55 Ho Chi Minh city','phat@gmail.com','0902444777','Quach Du Danh','33 Ha Noi city','0902444555',4000)
insert into Mail values('2','2','2','2','2','10000','11/15/2011','11/20/2011','Pending','No','Nguyen Thanh Binh','47 District 2 Nguyen Thi Dinh','binh@gmail.com','0902333666','Nguyen Huy Vu','123 District 1 Tran Hung Dao','0902222333',15000)
insert into Mail values('3','3','3','3','3','15000','11/15/2011','11/20/2011','Received','Yes','Chu Dai Hung','321 District 3 Ho Chi Minh city','hung@gmail.com','0902555888','Le Ngoc Thanh Hai','123 Ha Noi city','0902111222',16000)

